package jsyf.oa.dao.report.mineCustomer;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.report.mineCustomer.CustomerReportEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import jsyf.oa.service.report.reportCommon.ReportCommonMethodsAndConstant;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by 92852 on 2018/4/5.
 */
@Repository
public class CustomerReportDao extends BaseDao {
    @Autowired
    private ReportCommonMethodsAndConstant common;

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, CustomerReportEntity.class);
    }

    public CustomerReportDao() {
        super.iBaseEntity = new CustomerReportEntity();
    }

    /*获取个人*/
    public List<JSONObject> getForPersonalCustomerList(String condition, int requestPage, int pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "`ID` AS  id,\n" +
                "`NAME` AS  name,\n" +
                "TEL AS tel,\n" +
                "SOURCE AS source,\n" +
                "CUSTOMER_TYPE as customerType,\n" +
                "CUSTOMER_CREDIT as customerCredit,\n" +
                "PROVINCE_NAME AS provinceName,\n" +
                "CITY_NAME AS cityName,\n" +
                "COUNTY_NAME AS countyName,\n" +
                "INTENTION_MODEL AS intentionModel,\n" +
                "BUY_DATE AS buyDate,\n" +
                "BUY_TYPE AS buyType,\n" +
                "THROW_REASON AS throwReason,\n" +
                "INTENTION_FLAG AS intentionFlag,\n" +
                "INTENTION_TIME AS intentionTime,\n" +
                "EXISTING_DEVICE AS existingDevice,\n" +
                "FUND_CONDITION AS fundCondition,\n" +
                "CREATE_TIME AS createTime,\n" +
                "OTHER_CONDITION AS otherCondition,\n" +
                "concat_ws('-',CITY_NAME, COUNTY_NAME) as customerlocation\n" +
                "FROM\n" +
                "\tt_wrt_customer\n" +
                "WHERE 1=1\n");
        sb.append(condition);
        sb.append("GROUP BY\n" +
                "`NAME`,\n" +
                "TEL,\n" +
                "SOURCE ,\n" +
                "INTENTION_MODEL ,\n" +
                "BUY_DATE ,\n" +
                "BUY_TYPE ,\n" +
                "EXISTING_DEVICE,\n" +
                "FUND_CONDITION ,\n" +
                "OTHER_CONDITION ");
        sb.append("ORDER BY CREATE_TIME DESC");
        super.appendPageSql(sb, requestPage, pageSize);
        List<JSONObject> records = super.queryForJsonList(sb.toString());
        return records;
    }

    /*查询个人数量*/
    public Integer getCountPersonalCustomerList(String userId, String dateTime, String key) {
        StringBuffer condition = new StringBuffer();
        condition.append("SELECT\n" +
                "count(1) AS count\n" +
                "FROM\n" +
                "\tt_wrt_customer\n" +
                "WHERE 1=1\n" +
                " AND CREATE_USER_ID=" + "'" + userId + "'" + "\n");
        if (!StringUtils.isBlank(dateTime)) {
            condition.append("AND CREATE_TIME LIKE" + "'" + dateTime + "%'" + "\n");
        }
        if (!StringUtils.isBlank(key)) {
            condition.append("AND (`NAME` LIKE" + "'%" + key + "%'" + "OR(TEL LIKE" + "'%" + key + "%'" + "))" + "\n");
        }

        String count = super.queryForString(condition.toString());
        if (count == null) {
            count = "0";
        }
        return Integer.parseInt(count);
    }

    /*获取部门*/
    public List<JSONObject> getForPersonalCustomerList(String userId, String dateTime) {
        StringBuffer condition = new StringBuffer();
        condition.append("SELECT\n" +
                "sysCustomer.regionId,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo,\n" +
                "t_sys_role_name.SORT_NO AS roleNameSortNo\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID" + "\n");
        condition.append("WHERE 1=1\n" +
                "AND (t_sys_user.JOB_STATE<>1 OR(t_sys_user.JOB_STATE=1 AND t_sys_user_leave.LEAVE_DATE>" + " '" + dateTime + "'))" + "\n");
        if (!StringUtils.isBlank(dateTime)) {
            condition.append("AND t_wrt_customer.CREATE_TIME LIKE" + " '" + dateTime + "%'" + "\n");
        }
        condition.append("AND (\n" +
                "  t_sys_user.ID =" + " '" + userId + "'" + "\n");
        condition.append("OR t_sys_user.PARENT_ID = " + " '" + userId + "'" + "\n");
        condition.append("OR (\n" +
                "\t\t\t\t\tt_sys_user.ID in(\n" +
                "\t\t\t\t\t\t\tSELECT USER_ID FROM t_sys_user_2_region \n" +
                "\t\t\t\t\t\t\tWHERE \n" +
                "\t\t\t\t\t\t\tREGION_ID in(\n" +
                "\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID =" + " '" + userId + "'" + "\n");
        condition.append("\t)\n" +
                "\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND t_sys_role_name.FRONT_FLAG = 1\n" +
                "\t\t\t\t)\n" +
                ")\n" +
                "GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName\n" +
                "ORDER BY sysCustomer.departSortNo,sysCustomer.roleNameSortNo");
        List<JSONObject> records = super.queryForJsonList(condition.toString(), new Object[]{});
        return records;
    }

    /*获取公司*/
    public List<JSONObject> getForCompanyCustomerList(String userId, String dateTime) {
        StringBuffer condition = new StringBuffer();
        condition.append("SELECT\n" +
                "sysCustomer.regionId ,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "\n" +
                "WHERE 1=1\n" +
                "AND (t_sys_user.JOB_STATE<>1 OR( t_sys_user.JOB_STATE<>1 AND t_sys_user_leave.LEAVE_DATE>" + "'" + dateTime + "'))" + "\n");
        if (!StringUtils.isBlank(dateTime)) {
            condition.append("AND t_wrt_customer.CREATE_TIME LIKE" + " '" + dateTime + "%'" + "\n");
        }
        condition.append("AND t_sys_depart.COMPANY_ID= (\n" +
                "\n" +
                "SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID =" + "'" + userId + "')))" + "\n");
        condition.append("GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName\n" +
                "ORDER BY\n" +
                "sysCustomer.departSortNo");
        List<JSONObject> records = super.queryForJsonList(condition.toString(), new Object[]{});
        return records;
    }

    /*查询用户manageFlag*/
    public Integer getManageFlagByUserId(String userId) {
        StringBuffer condition = new StringBuffer();
        condition.append(
                "SELECT DISTINCT(manageFlag) FROM v_sys_user\n" +
                        "WHERE id='" + userId + "'");
        String manageFlag = super.queryForString(condition.toString());
        return Integer.parseInt(manageFlag);
    }

    /*集团*/
    public List<JSONObject> getForGroupCustomerList(String userId, String dateTime, String sql) {
        StringBuffer condition = new StringBuffer();
        condition.append("SELECT\n" +
                "company.companyId AS companyId,\n" +
                "company.companyName AS companyName,\n" +
                "IFNULL(newCustomer.newCount,0) as num,\n" +
                "IFNULL(allCustomer.allCount,0) as allNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT \n" +
                "\tID AS companyId,\n" +
                "`NAME` AS companyName,\n" +
                "SORT_NO AS sortNo\n" +
                "FROM \n" +
                "t_sys_company\n" +
                "WHERE  ID in( " + sql + ")" + ") AS company" + "\n");
        condition.append("LEFT JOIN\n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_depart.COMPANY_ID AS companyId,\n" +
                "COUNT(1) AS newCount\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE 1=1\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND (t_sys_user.JOB_STATE<>1 OR( t_sys_user.JOB_STATE<>1 AND t_sys_user_leave.LEAVE_DATE>'" + dateTime + "'))\n");
        if (!StringUtils.isBlank(dateTime)) {
            condition.append("AND t_wrt_customer.CREATE_TIME LIKE" + " '" + dateTime + "%'" + "\n");
        }
        condition.append("AND\t\tt_sys_depart.COMPANY_ID  in(" + sql + ")" + "\n");
        condition.append("GROUP BY\n" +
                "t_sys_depart.COMPANY_ID\n" +
                "\n" +
                ") AS newCustomer ON newCustomer.companyId=company.companyId\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_depart.COMPANY_ID AS companyId,\n" +
                "COUNT(1) AS allCount\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "WHERE 1=1\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND\t\tt_sys_depart.COMPANY_ID  in(" + sql + ")" + "\n");
        condition.append("GROUP BY\n" +
                "t_sys_depart.COMPANY_ID) AS allCustomer ON allCustomer.companyId=company.companyId\n" +
                "\n" +
                "ORDER BY company.sortNo\n" +
                "\n");
        List<JSONObject> records = super.queryForJsonList(condition.toString());
        return records;
    }

    /*获取身份标志manageFlag,frontFlag,areaType,*/
    public JSONObject getManageFlagAndFrontFlagAndAreaTypeByUserId(String userId) {
        String sql = "SELECT \n" +
                "t_sys_user.ID as userId,\n" +
                "t_sys_user.`NAME` as userName,\n" +
                "t_sys_role_name.FRONT_FLAG as frontFlag,\n" +
                "t_sys_role_name.AREA_TYPE as areaType,\n" +
                "t_sys_role_name.MANAGE_FLAG as manageFlag\n" +
                " from t_sys_user \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE t_sys_user.`ID` = '" + userId + "'";
        JSONObject objUser = super.queryForJsonObject(sql, null);
        return objUser;
    }

    /*获取所有客户*/
    public List<JSONObject> getAllCustomerList(String userId, String key, String companyId, int requestPage, int pageSize, JSONObject roleFlag) {
        String sqlCustomer = "";
        /*查询所有客户*/
        if (roleFlag.getString("manageFlag").equals("1")) {
            //集团管理员
            sqlCustomer = "SELECT\n" +
                    "t_wrt_customer.`NAME` AS  name,\n" +
                    "t_wrt_customer.`ID` AS  id,\n" +
                    "t_wrt_customer.TEL AS tel,\n" +
                    "t_wrt_customer.SOURCE AS source,\n" +
                    "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                    "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                    "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                    "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                    "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                    "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                    "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                    "t_wrt_customer.FINALLY_VISIT_TIME AS finallyVisitTime,\n" +
                    "t_wrt_customer.CREATE_TIME AS createTime,\n" +
                    "t_wrt_customer.FINALLY_VISIT_USER_ID AS finallyVisitUserId\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%')" + "\n" +
                    "AND t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                    "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT \n" +
                    "t_sys_user.ID AS id\n" +
                    "FROM \n" +
                    "t_sys_user\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "WHERE 1=1\n" +
                    "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                    "GROUP BY \n" +
                    "t_wrt_customer.ID\n" +
                    "order by \n" +
                    "t_wrt_customer.CREATE_TIME";

        } else if (roleFlag.getString("frontFlag").equals("1")) {
            if (roleFlag.getString("areaType").equals("1")) {
                //大区经理
                sqlCustomer = "SELECT\n" +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                        "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                        "t_wrt_customer.FINALLY_VISIT_TIME AS finallyVisitTime,\n" +
                        "t_wrt_customer.CREATE_TIME AS createTime,\n" +
                        "t_wrt_customer.FINALLY_VISIT_USER_ID AS finallyVisitUserId\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "WHERE 1=1\n" +
                        "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                        "\n" +
                        "SELECT \n" +
                        "id \n" +
                        "FROM v_sys_user\n" +
                        "WHERE 1=1" + "\n" +
                        "AND( (parentId=" + "'" + userId + "')" + "\n" +
                        "OR(id=" + "'" + userId + "'" + "))" + "\n" +
                        "AND v_sys_user.jobState<>1" + "\n" +
                        "GROUP BY \n" +
                        "id\n" +
                        "\n" +
                        ")\n" +
                        "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + "OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                        "GROUP BY \n" +
                        "t_wrt_customer.`NAME`,\n" +
                        "t_wrt_customer.TEL,\n" +
                        "t_wrt_customer.SOURCE ,\n" +
                        "t_wrt_customer.INTENTION_MODEL ,\n" +
                        "t_wrt_customer.BUY_DATE ,\n" +
                        "t_wrt_customer.BUY_TYPE ,\n" +
                        "t_wrt_customer.EXISTING_DEVICE,\n" +
                        "t_wrt_customer.FUND_CONDITION ,\n" +
                        "t_wrt_customer.OTHER_CONDITION \n"
                        + " order by t_wrt_customer.CREATE_TIME ";
            } else {
                //区域经理
                sqlCustomer = "select " +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                        "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                        "t_wrt_customer.FINALLY_VISIT_TIME AS finallyVisitTime,\n" +
                        "t_wrt_customer.CREATE_TIME AS createTime,\n" +
                        "t_wrt_customer.FINALLY_VISIT_USER_ID AS finallyVisitUserId\n" +
                        " from t_wrt_customer where 1=1" + "\n" +
                        "AND ID in (\n" +
                        "     SELECT CUSTOMER_ID FROM t_wrt_customer_visit WHERE CREATE_USER_ID='" + userId + "')\n" +
                        "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n"
                        + "GROUP BY \n" +
                        "t_wrt_customer.`NAME`,\n" +
                        "t_wrt_customer.TEL,\n" +
                        "t_wrt_customer.SOURCE ,\n" +
                        "t_wrt_customer.INTENTION_MODEL ,\n" +
                        "t_wrt_customer.BUY_DATE ,\n" +
                        "t_wrt_customer.BUY_TYPE ,\n" +
                        "t_wrt_customer.EXISTING_DEVICE,\n" +
                        "t_wrt_customer.FUND_CONDITION ,\n" +
                        "t_wrt_customer.OTHER_CONDITION " + "\n"
                        + " order by t_wrt_customer.CREATE_TIME " + "\n";
            }
        } else {
            //其他岗位
            sqlCustomer = " SELECT\n" +
                    "t_wrt_customer.`NAME` AS  name,\n" +
                    "t_wrt_customer.`ID` AS  id,\n" +
                    "t_wrt_customer.TEL AS tel,\n" +
                    "t_wrt_customer.SOURCE AS source,\n" +
                    "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                    "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                    "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                    "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                    "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                    "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                    "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                    "t_wrt_customer.FINALLY_VISIT_TIME AS finallyVisitTime,\n" +
                    "t_wrt_customer.CREATE_TIME AS createTime,\n" +
                    "t_wrt_customer.FINALLY_VISIT_USER_ID AS finallyVisitUserId\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT\n" +
                    "t_sys_user.ID\n" +
                    "FROM\n" +
                    "t_sys_user\n" +
                    "LEFT JOIN  t_sys_user_2_region  ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                    "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                    "WHERE 1=1\n" +
                    "AND t_sys_user_2_region.REGION_ID  in(\n" +
                    "SELECT REGION_ID FROM t_sys_user_2_region \n" +
                    "WHERE USER_ID=" + "'" + userId + "')" + "\n" +
                    "AND  t_sys_user.JOB_STATE<>1 \n" +
                    "GROUP BY \n" +
                    "t_sys_user.ID\n" +
                    "\n" + ")" +
                    "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                    "GROUP BY \n" +
                    "t_wrt_customer.ID\n" +
                    " order by t_wrt_customer.CREATE_TIME";
        }
        StringBuffer sqlBuffer = new StringBuffer(sqlCustomer);
        super.appendPageSql(sqlBuffer, Integer.valueOf(requestPage), Integer.valueOf(pageSize));
        List<JSONObject> records = super.queryForJsonList(sqlBuffer.toString());
        return records;
    }

    /*获取查询数目*/
    public Integer getCountByCompanyIdAndUserIdAndKey(String companyId, String userId, String key, JSONObject roleFlag) {
        int recordCount = 0;
        String sqlCustomer = "";
        if (roleFlag.getString("manageFlag").equals("1")) {
            String all = "SELECT\n" +
                    "COUNT(1) AS count\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                    "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT \n" +
                    "t_sys_user.ID AS id\n" +
                    "FROM \n" +
                    "t_sys_user\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "WHERE 1=1\n" +
                    "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                    "AND(t_wrt_customer.`NAME` LIKE  " + " '%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE  " + " '%" + key + "%'" + ")";
            JSONObject objCustomer = super.queryForJsonObject(all, null);
            recordCount = objCustomer.getInteger("count");
        } else {
            if (roleFlag.getString("manageFlag").equals("1")) {
                //集团管理员
                sqlCustomer =
                        "SELECT COUNT(1) AS  count\n" +
                                "from (\n" +
                                "SELECT\n" +
                                "t_wrt_customer.`NAME` AS  name,\n" +
                                "t_wrt_customer.`ID` AS  id,\n" +
                                "t_wrt_customer.TEL AS tel,\n" +
                                "t_wrt_customer.SOURCE AS source,\n" +
                                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                                "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                                "FROM\n" +
                                "\tt_wrt_customer\n" +
                                "WHERE 1=1\n" +
                                "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%')" + "\n" +
                                "AND t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                                "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                                "SELECT \n" +
                                "t_sys_user.ID AS id\n" +
                                "FROM \n" +
                                "t_sys_user\n" +
                                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                                "WHERE 1=1\n" +
                                "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                                "GROUP BY \n" +
                                "t_wrt_customer.ID\n" +
                                "order by \n" +
                                "t_wrt_customer.CREATE_TIME\n" + ")AS sys";
            } else if (roleFlag.getString("frontFlag").equals("1")) {
                if (roleFlag.getString("areaType").equals("1")) {
                    //大区经理
                    sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                            "from (\n" +
                            "SELECT\n" +
                            "t_wrt_customer.`NAME` AS  name,\n" +
                            "t_wrt_customer.`ID` AS  id,\n" +
                            "t_wrt_customer.TEL AS tel,\n" +
                            "t_wrt_customer.SOURCE AS source,\n" +
                            "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                            "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                            "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                            "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                            "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                            "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                            "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                            "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                            "FROM\n" +
                            "\tt_wrt_customer\n" +
                            "WHERE 1=1\n" +
                            "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                            "\n" +
                            "SELECT \n" +
                            "id \n" +
                            "FROM v_sys_user\n" +
                            "WHERE 1=1" + "\n" +
                            "AND( (parentId=" + "'" + userId + "')" + "\n" +
                            "OR(id=" + "'" + userId + "'" + "))" + "\n" +
                            "AND v_sys_user.jobState<>1" + "\n" +
                            "GROUP BY \n" +
                            "id\n" +
                            "\n" +
                            ")\n" +
                            "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + "OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                            "GROUP BY \n" +
                            "t_wrt_customer.`NAME`,\n" +
                            "t_wrt_customer.TEL,\n" +
                            "t_wrt_customer.SOURCE ,\n" +
                            "t_wrt_customer.INTENTION_MODEL ,\n" +
                            "t_wrt_customer.BUY_DATE ,\n" +
                            "t_wrt_customer.BUY_TYPE ,\n" +
                            "t_wrt_customer.EXISTING_DEVICE,\n" +
                            "t_wrt_customer.FUND_CONDITION ,\n" +
                            "t_wrt_customer.OTHER_CONDITION \n"
                            + " order by t_wrt_customer.CREATE_TIME\n " + ") AS sys";
                } else {
                    //区域经理
                    sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                            "from (\n" +
                            "select\n " +
                            "t_wrt_customer.`NAME` AS  name,\n" +
                            "t_wrt_customer.`ID` AS  id,\n" +
                            "t_wrt_customer.TEL AS tel,\n" +
                            "t_wrt_customer.SOURCE AS source,\n" +
                            "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                            "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                            "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                            "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                            "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                            "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                            "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                            "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                            " from t_wrt_customer where 1=1" + "\n" +
                            "AND CREATE_USER_ID=" + "'" + userId + "'" + "\n" +
                            "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n"
                            + "GROUP BY \n" +
                            "t_wrt_customer.`NAME`,\n" +
                            "t_wrt_customer.TEL,\n" +
                            "t_wrt_customer.SOURCE ,\n" +
                            "t_wrt_customer.INTENTION_MODEL ,\n" +
                            "t_wrt_customer.BUY_DATE ,\n" +
                            "t_wrt_customer.BUY_TYPE ,\n" +
                            "t_wrt_customer.EXISTING_DEVICE,\n" +
                            "t_wrt_customer.FUND_CONDITION ,\n" +
                            "t_wrt_customer.OTHER_CONDITION " + "\n"
                            + " order by t_wrt_customer.CREATE_TIME\n " + ") AS sys";
                }
            } else {
                //其他岗位
                sqlCustomer =
                        "SELECT COUNT(1) AS  count\n" +
                                "from (\n" +
                                " SELECT\n" +
                                "t_wrt_customer.`NAME` AS  name,\n" +
                                "t_wrt_customer.`ID` AS  id,\n" +
                                "t_wrt_customer.TEL AS tel,\n" +
                                "t_wrt_customer.SOURCE AS source,\n" +
                                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                                "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                                "FROM\n" +
                                "\tt_wrt_customer\n" +
                                "WHERE 1=1\n" +
                                "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                                "SELECT\n" +
                                "t_sys_user.ID\n" +
                                "FROM\n" +
                                "t_sys_user\n" +
                                "LEFT JOIN  t_sys_user_2_region  ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                                "WHERE 1=1\n" +
                                "AND t_sys_user_2_region.REGION_ID  in(\n" +
                                "SELECT REGION_ID FROM t_sys_user_2_region \n" +
                                "WHERE USER_ID=" + "'" + userId + "')" + "\n" +
                                "AND  t_sys_user.JOB_STATE<>1 \n" +
                                "GROUP BY \n" +
                                "t_sys_user.ID\n" +
                                "\n" + ")" +
                                "AND(t_wrt_customer.`NAME` LIKE " + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                                "GROUP BY \n" +
                                "t_wrt_customer.ID\n" +
                                " order by t_wrt_customer.CREATE_TIME\n" + ") AS sys";
            }
            String count = super.queryForString(sqlCustomer.toString());
            recordCount = Integer.parseInt(count);
        }
        return recordCount;
    }

    /*集团到部门*/
    public List<JSONObject> getDepartByCompanyId(String companyId, String dateTime) {
        StringBuffer condition = new StringBuffer();
        condition.append("SELECT\n" +
                "sysCustomer.regionId,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "\n" +
                "WHERE 1=1\n" +
                "AND (t_sys_user.JOB_STATE<>1 OR( t_sys_user.JOB_STATE<>1 AND t_sys_user_leave.LEAVE_DATE>" + "'" + dateTime + "'))" + "\n");
        if (!StringUtils.isBlank(dateTime)) {
            condition.append("AND t_wrt_customer.CREATE_TIME LIKE" + " '" + dateTime + "%'" + "\n");
        }
        condition.append("AND t_sys_depart.COMPANY_ID= " + "'" + companyId + "'" + "\n");
        condition.append("GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName" + "\n");
        condition.append("ORDER BY sysCustomer.departSortNo");
        List<JSONObject> records = super.queryForJsonList(condition.toString(), new Object[]{});
        return records;
    }

    /*根据公司ID获得全部客户*/
    public List<JSONObject> getAllCustomerListByCompanyId(String companyId, String key, int requestPage, int pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "t_wrt_customer.`ID` AS  id,\n" +
                "t_wrt_customer.`NAME` AS  name,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.SOURCE AS source,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                "t_wrt_customer.CREATE_TIME AS createTime,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                "from t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_wrt_customer.CREATE_USER_ID = t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role on t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "WHERE t_wrt_customer.CREATE_USER_ID is not NULL \n" +
                "AND t_sys_depart.COMPANY_ID =" + "'" + companyId + "'" + "\n" +
                "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")" + "\n" +
                "GROUP BY \n" +
                "t_wrt_customer.ID\n");
        sb.append(" order by t_wrt_customer.CREATE_TIME");
        super.appendPageSql(sb, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    /*根据客户ID获得客户信息*/
    public JSONObject getCustomerById(String customerId) {
        String customerInfoSql = "SELECT\n" +
                "NAME AS name,\n" +
                "ID AS id,\n" +
                "TEL AS tel,\n" +
                "WECHAT AS wechat,\n" +
                "CUSTOMER_TYPE as customerType,\n" +
                "CUSTOMER_LEVEL as customerLevel,\n" +
                "BIRTHDAY AS birthday,\n" +
                "ID_CARD AS idCard,\n" +
                "PROVINCE_NAME AS provinceName,\n" +
                "CITY_NAME  AS cityName,\n" +
                "COUNTY_NAME AS countyName,\n" +
                "SOURCE AS source,\n" +
                "INTENTION_MODEL AS intentionModel,\n" +
                "BUY_TYPE AS buyType,\n" +
                "BUY_DATE  AS buyDate,\n" +
                "FUND_CONDITION AS fundCondition,\n" +
                "EXISTING_DEVICE AS existingDevice,\n" +
                "OTHER_CONDITION AS otherCondition,\n" +
                "CUSTOMER_CREDIT AS customerCredit,\n" +
                "CUSTOMER_LEVEL AS customerLevel,\n" +
                "CUSTOMER_TYPE AS customerType,\n" +
                "INTENTION_BRAND AS intentionBrand,\n" +
                "COMPETITOR_CONDITION AS competitorCondition,\n" +
                "THROW_REASON AS throwReason,\n" +
                "CHANCE AS chance,\n" +
                "BUSINESS_DATE AS businessDate,\n" +
                "INTENTION_FLAG AS intentionFlag,\n" +
                "INTENTION_DEVICE AS intentionDevice\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "WHERE\n" +
                "ID=" + "'" + customerId + "'";
          /*客户信息*/
        JSONObject customerInfo = super.queryForJsonObject(customerInfoSql, null);
        return customerInfo;
    }

    /*根据客户ID获得拜访信息*/
    public List<JSONObject> getVisitCustomerInfoByCustomerId(String customerId) {
        /*跟踪记录sql*/
        String customerVisitSql = "SELECT\n" +
                "t_sys_user.`NAME` AS createName,\n" +
                "t_wrt_customer_visit.`ID` AS customerVisitId,\n" +
                "t_wrt_customer_visit.CREATE_TIME AS createTime,\n" +
                "t_wrt_customer_visit.VISIT_MODE AS visitModel,\n" +
                "t_wrt_customer_visit.OTHER_CONDITION AS otherCondition\n" +
                "FROM\n" +
                "t_wrt_customer_visit\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer_visit.CREATE_USER_ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID=" + "'" + customerId + "'\n" +
                "ORDER BY  t_wrt_customer_visit.CREATE_TIME desc";
        /*跟踪信息*/
        List<JSONObject> customerVisitInfo = super.queryForJsonList(customerVisitSql, new Object[]{});
        return customerVisitInfo;
    }

    /*根据公司ID获得该公司下所有客户数量*/
    public Integer getCountCustomerListByCompanyId(String companyId, String key) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "COUNT(1) AS count\n" +
                "from t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_wrt_customer.CREATE_USER_ID = t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role on t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "WHERE t_wrt_customer.CREATE_USER_ID is not NULL \n" +
                "AND t_sys_depart.COMPANY_ID =" + "'" + companyId + "'" + "\n" +
                "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")"
        );
        String count = super.queryForString(sb.toString());
        return Integer.parseInt(count);
    }

    /*根据导入的exclex修改客户信息*/
    public Integer updateFromExcle(String name, String tel, String customerType, String customerCredit, String birthday, String wechat, String cityName, String countryName, String existingDevice) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE t_wrt_customer\n" +
                "SET `NAME`='" + name + "',\n" +
                "CUSTOMER_TYPE='" + customerType + "',\n" +
                "CUSTOMER_CREDIT='" + customerCredit + "',\n" +
                "BIRTHDAY='" + birthday + "',\n" +
                "WECHAT='" + wechat + "',\n" +
                "CITY_NAME='" + cityName + "',\n" +
                "COUNTY_NAME='" + countryName + "',\n" +
                "EXISTING_DEVICE='" + existingDevice + "'\n" +
                "WHERE 1=1\n" +
                "AND TEL='" + tel + "'");
        return this.getJdbcTemplate().update(sql.toString());
    }

    public JSONObject getIdByNameAndTel(String name,String tel) {
        String customerInfoSql = "SELECT id FROM v_sys_user \n" +
                "WHERE 1=1\n" +
                " AND `name`='"+name+"' \n" +
                "AND tel='"+tel+"' ";
        JSONObject id = super.queryForJsonObject(customerInfoSql, null);
        return id;
    }



}
